<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.text.DateFormat"%>
<%@ page import = "util.UserMethod" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="factory.OracleDAOFactory"%>
<%@ page import = "java.sql.*" %>

  <%
      // set content type and xml tag
      response.setContentType("text/xml");
      out.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
 
      // define variables from incoming values
      String posStart = "";
      if (request.getParameter("posStart") != null){
          posStart = request.getParameter("posStart");
      }else{
          posStart = "0";
      }   
 
      String count = "";
      if (request.getParameter("count") != null){
          count = request.getParameter("count");
      }else{
          count = "100";   
      }   
 
      // connect to database
      	Connection conn = null;		
		Statement  stmt = null;
		ResultSet rs = null;
		conn = OracleDAOFactory.createConnection();
		
      // query to products table
      String sql = "SELECT * FROM employee e, employee_info ei WHERE e.employee_id = ei.employee_id";
 
      // if this is the first query - get total number of records in the query result
      String totalCount = "";
      if (posStart.equals("0")){
          String sqlCount = "Select count(*) as cnt from employee";
          stmt = conn.createStatement();
          rs = stmt.executeQuery(sqlCount);
          rs.next();
          totalCount = rs.getString("cnt");
          rs.close();
      } else {
          totalCount = "";
      }
 
      // add limits to query to get only rows necessary for output
      sql += " AND rownum BETWEEN " + posStart + " and " + count;
 
      // Execute the query
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
 
      // output data in XML format  
      out.println("<rows>");
      out.println("<head>");
      out.println("<column width='60' type='ed' align='center' sort='str'>사번</column>");
      out.println("<column width='60' type='ed' align='center' sort='str'>직급</column>");
      out.println("<column width='60' type='ed' align='center' sort='str'>이름</column>");
      out.println("<column width='50' type='ed' align='center' sort='str'>성별</column>");
      out.println("<column width='80' type='ed' align='center' sort='str'>생년월일</column>");
      out.println("<column width='90' type='ed' align='center' sort='str'>핸드폰</column>");
      out.println("<column width='150' type='ed' align='center' sort='str'>이메일</column>");
      out.println("<column width='*' type='ed' align='center' sort='str'>주소</column>");
      out.println("<column width='70' type='ed' align='center' sort='str'>입사일</column>");
      out.println("</head>");
      request.setCharacterEncoding("UTF-8");
      DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
      UserMethod um = new UserMethod();
      while (rs.next()) {
          out.println("<row id='" + rs.getString("employee_info_id") + "'>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("employee_id"))); 
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("position")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("employee_kname")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("gender")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(df.format(rs.getDate("birthday")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("phonecel")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("email")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("address")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(df.format(rs.getDate("hiredate")));
              out.println("</cell>");
          out.println("</row>");
      }
      out.println("</rows>");
      rs.close();
      conn.close();
      stmt.close();

  %>
  
  